#!/bin/bash
APP=edu

if [ -n "$2" ] ;then
   do_date=$2
else 
   echo "请传入日期参数"
   exit
fi

dws_trade_user_course_order_nd="
insert overwrite table ${APP}.dws_trade_course_order_nd partition(dt='$do_date')
select
  course_id,
  course_name,
  subject_id,
  subject_name,
  category_id,
  category_name,
  sum(if(dt>=date_add('$do_date',-6),order_count_1d,0)),
  sum(if(dt>=date_add('$do_date',-6),1,0)),
  sum(if(dt>=date_add('$do_date',-6),order_original_amount_1d,0)),
  sum(if(dt>=date_add('$do_date',-6),coupon_reduce_amount_1d,0)),
  sum(if(dt>=date_add('$do_date',-6),order_total_amount_1d,0)),
  sum(order_count_1d),
  sum(1),
  sum(order_original_amount_1d),
  sum(coupon_reduce_amount_1d),
  sum(order_total_amount_1d)
from ${APP}.dws_trade_user_course_order_1d
where dt <='$do_date'
and dt >= date_add('$do_date',-29)
group by course_id,course_name,subject_id,subject_name,category_id,category_name;
"
dws_trade_user_order_nd="
insert overwrite table ${APP}.dws_trade_user_order_nd partition(dt='$do_date')
select
  user_id,
  province_id,
  sum(if(dt>=date_add('$do_date',-6),order_count_1d,0)),
  sum(if(dt>=date_add('$do_date',-6),order_num_1d,0)),
  sum(if(dt>=date_add('$do_date',-6),order_original_amount_1d,0)),
  sum(if(dt>=date_add('$do_date',-6),coupon_reduce_amount_1d,0)),
  sum(if(dt>=date_add('$do_date',-6),order_total_amount_1d,0)),
  sum(order_count_1d),
  sum(order_num_1d),
  sum(order_original_amount_1d),
  sum(coupon_reduce_amount_1d),
  sum(order_total_amount_1d)
from ${APP}.dws_trade_user_order_1d
where dt>=date_add('$do_date',-29)
and dt<='$do_date'
group by user_id,province_id;
"
dws_trade_user_cart_add_nd="
insert overwrite table ${APP}.dws_trade_user_cart_add_nd partition(dt='$do_date')
select
    user_id,
    sum(if(dt>=date_add('$do_date',-6),cart_add_count_1d,0)),
    sum(cart_add_count_1d)
from ${APP}.dws_trade_user_cart_add_1d
where dt>=date_add('$do_date',-29)
and dt<='$do_date'
group by user_id;
"
dws_trade_user_payment_nd="
insert overwrite table ${APP}.dws_trade_user_payment_nd partition (dt='$do_date')
select user_id,
       sum(if(dt >= date_add('$do_date', -6), payment_count_1d, 0)),
       sum(if(dt >= date_add('$do_date', -6), payment_amount_1d, 0)),
       sum(payment_count_1d),
       sum(payment_amount_1d)
from ${APP}.dws_trade_user_payment_1d
where dt>=date_add('$do_date', -29)
  and dt<='$do_date'
group by user_id;
"
dws_trade_province_order_nd="
insert overwrite table ${APP}.dws_trade_province_order_nd partition(dt='$do_date')
select
    province_id,
    province_name,
    iso_code,
    iso_3166_2,
    sum(if(dt>=date_add('$do_date',-6),order_count_1d,0)),
    sum(if(dt>=date_add('$do_date',-6),order_original_amount_1d,0)),
    sum(if(dt>=date_add('$do_date',-6),order_final_amount_1d,0)),
    sum(order_count_1d),
    sum(order_original_amount_1d),
    sum(order_final_amount_1d)
from ${APP}.dws_trade_province_order_1d
where dt>=date_add('$do_date',-29)
and dt<='$do_date'
group by province_id,province_name,iso_code,iso_3166_2;
"
dws_traffic_page_visitor_page_view_nd="
insert overwrite table ${APP}.dws_traffic_page_visitor_page_view_nd partition(dt='$do_date')
select
    mid_id,
    brand,
    model,
    operate_system,
    page_id,
    sum(if(dt>=date_add('$do_date',-6),during_time_1d,0)),
    sum(if(dt>=date_add('$do_date',-6),view_count_1d,0)),
    sum(during_time_1d),
    sum(view_count_1d)
from ${APP}.dws_traffic_page_visitor_page_view_1d
where dt>=date_add('$do_date',-29)
and dt<='$do_date'
group by mid_id,brand,model,operate_system,page_id;
"
dws_interaction_paper_course_test_exam_nd="
insert overwrite table ${APP}.dws_interaction_paper_course_test_exam_nd partition(dt='$do_date')
select
    paper_id,
    course_id,
    sum(if(dt>=date_add('$do_date',-6),user_count_1d,0)),
    sum(if(dt>=date_add('$do_date',-6),sum_score_1d,0)),
    sum(if(dt>=date_add('$do_date',-6),sum_duration_sec_1d,0)),
    sum(if(dt>=date_add('$do_date',-6),0_to_20_count,0)),
    sum(if(dt>=date_add('$do_date',-6),20_to_40_count,0)),
    sum(if(dt>=date_add('$do_date',-6),40_to_60_count,0)),
    sum(if(dt>=date_add('$do_date',-6),60_to_80_count,0)),
    sum(if(dt>=date_add('$do_date',-6),80_to_100_count,0)),
    sum(user_count_1d),
    sum(sum_score_1d),
    sum(sum_duration_sec_1d),
    sum(0_to_20_count),
    sum(20_to_40_count),
    sum(40_to_60_count),
    sum(60_to_80_count),
    sum(80_to_100_count)
from ${APP}.dws_interaction_paper_course_test_exam_1d
where dt>=date_add('$do_date',-29)
and dt<='$do_date'
group by paper_id,course_id;
"
dws_interaction_question_test_exam_nd="
insert overwrite table ${APP}.dws_interaction_question_test_exam_nd partition(dt='$do_date')
select
    question_id,
    sum(if(dt>=date_add('$do_date',-6),answer_count_1d,0)),
    sum(if(dt>=date_add('$do_date',-6),answer_correct_1d,0)),
    sum(answer_count_1d),
    sum(answer_correct_1d)
from ${APP}.dws_interaction_question_test_exam_1d
where dt>=date_add('$do_date',-29)
and dt<='$do_date'
group by question_id;
"
dws_traffic_user_chapter_video_watch_video_nd="
insert overwrite table ${APP}.dws_traffic_user_chapter_video_watch_video_nd partition (dt='$do_date')
select
    user_id,
    chapter_id,
    video_id,
    duration_sec,
    course_id,
    sum(if(dt>=date_add('$do_date',-6),play_time_1d,0)),
    sum(if(dt>=date_add('$do_date',-6),sum_play_sec_1d,0)),
    max(if(dt>=date_add('$do_date',-6),max_position_sec_1d,0)),
    if(sum(if(dt>=date_add('$do_date',-6),sum_play_sec_1d,0)) >= (duration_sec*0.9) and max(if(dt>=date_add('$do_date',-6),max_position_sec_1d,0)) >= (duration_sec*0.9),1,0),
    sum(play_time_1d),
    sum(sum_play_sec_1d),
    max(max_position_sec_1d),
    if(sum(sum_play_sec_1d) >= (duration_sec*0.9) and max(max_position_sec_1d) >= (duration_sec*0.9),1,0)
from ${APP}.dws_traffic_user_chapter_video_watch_video_1d
where dt>=date_add('$do_date',-29)
and dt<='$do_date'
group by user_id,chapter_id,video_id,duration_sec,course_id;
"

case $1 in
    "dws_trade_user_course_order_nd" )
        hive -e "$dws_trade_user_course_order_nd"
    ;;
    "dws_trade_user_order_nd" )
        hive -e "$dws_trade_user_order_nd"
    ;;
    "dws_trade_user_cart_add_nd" )
        hive -e "$dws_trade_user_cart_add_nd"
    ;;
    "dws_trade_user_payment_nd" )
        hive -e "$dws_trade_user_payment_nd"
    ;;
    "dws_trade_province_order_nd" )
        hive -e "$dws_trade_province_order_nd"
    ;;
    "dws_traffic_page_visitor_page_view_nd" )
        hive -e "$dws_traffic_page_visitor_page_view_nd"
    ;;
    "dws_interaction_paper_course_test_exam_nd" )
        hive -e "$dws_interaction_paper_course_test_exam_nd"
    ;;
    "dws_interaction_question_test_exam_nd" )
        hive -e "$dws_interaction_question_test_exam_nd"
    ;;
    "dws_traffic_user_chapter_video_watch_video_nd" )
        hive -e "$dws_traffic_user_chapter_video_watch_video_nd"
    ;;
    "all" )
        hive -e "$dws_trade_user_course_order_nd$dws_trade_user_order_nd$dws_trade_user_cart_add_nd$dws_trade_user_payment_nd$dws_trade_province_order_nd$dws_traffic_page_visitor_page_view_nd$dws_interaction_paper_course_test_exam_nd$dws_interaction_question_test_exam_nd$dws_traffic_user_chapter_video_watch_video_nd"
    ;;
esac
